package com.apes.scm.account.account.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.account.account.model.Cooperation;
import com.apes.scm.account.account.model.InventoryBatch;
import com.apes.scm.masterdata.frame.model.Dept;
import com.apes.scm.masterdata.party.model.PartyRole;
import com.apes.scm.masterdata.product.model.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by HuJianlin on 2018-01-30.
 */
public interface InventoryBatchRepository extends CustomRepository<InventoryBatch, Long> {

//    List<InventoryBatch> findByCompanyAndProductAndBalanceQtyGreaterThanOrderByBatchNumberAscBatchSerialAsc(PartyRole company, Product product, double balanceQty);

//    List<InventoryBatch> findByCompanyAndSupplierAndProductAndBalanceQtyGreaterThanOrderByBatchNumberAscBatchSerialAsc(PartyRole company, PartyRole supplier, Product product, double balanceQty);

    @Query("select e from InventoryBatch e where e.company = :company and e.dept = :dept and e.product = :product and e.balanceQty - e.frostQty > 0 order by e.cooperation.priority asc, e.batchNumber asc, e.batchSerial asc")
    List<InventoryBatch> findInventoryBatchForSaleOrder(@Param("company") PartyRole company, @Param("dept") Dept dept, @Param("product") Product product);

    @Query("select e from InventoryBatch e where e.company = :company and e.dept = :dept and e.supplier = :supplier and e.product = :product and e.balanceQty - e.frostQty > 0 order by e.cooperation.priority asc, e.batchNumber asc, e.batchSerial asc")
    List<InventoryBatch> findInventoryBatchForSaleOrder(@Param("company") PartyRole company, @Param("dept") Dept dept, @Param("supplier") PartyRole supplier, @Param("product") Product product);

    @Query("select e from InventoryBatch e where e.company = :company and e.dept = :dept and e.supplier.id in :supplier and e.product = :product and e.balanceQty - e.frostQty > 0 order by e.cooperation.priority asc, e.batchNumber asc, e.batchSerial asc")
    List<InventoryBatch> findInventoryBatchForSaleOrder(@Param("company") PartyRole company, @Param("dept") Dept dept, @Param("supplier") List supplier, @Param("product") Product product);

    @Query("select e from InventoryBatch e " +
            " where e.company = :company and e.dept = :dept and e.supplier.id in :supplier and e.product = :product and e.balanceQty - e.frostQty > 0 " +
            "   AND e.supplier.party.id not in (select id from PartyRole where role.id = 'Company') " +
            "  order by e.cooperation.priority asc, e.batchNumber asc, e.batchSerial asc")
    List<InventoryBatch> findInvenBaForSaleOrderNotInnerSupplier(@Param("company") PartyRole company, @Param("dept") Dept dept, @Param("supplier") List supplier, @Param("product") Product product);

    @Query("select e from InventoryBatch e where e.company = :company and e.dept = :dept and e.product = :product and e.salesQty > 0 order by e.cooperation.priority desc, e.batchNumber desc, e.batchSerial desc")
    List<InventoryBatch> findInventoryBatchForSaleReturn(@Param("company") PartyRole company, @Param("dept") Dept dept, @Param("product") Product product);

    @Query("select e from InventoryBatch e where e.company = :company and e.dept = :dept and e.supplier = :supplier and e.product = :product and e.salesQty > 0 order by e.cooperation.priority desc, e.batchNumber desc, e.batchSerial desc")
    List<InventoryBatch> findInventoryBatchForSaleReturn(@Param("company") PartyRole company, @Param("dept") Dept dept, @Param("supplier") PartyRole supplier, @Param("product") Product product);

    @Query("select e from InventoryBatch e where e.company = :company and e.dept = :dept and e.supplier = :supplier and e.cooperation = :cooperation and e.product = :product and e.balanceQty - e.frostQty > 0 order by e.batchNumber asc, e.batchSerial asc ")
    List<InventoryBatch> findInventoryBatchForPurchaseReturn(@Param("company") PartyRole company, @Param("dept") Dept dept, @Param("supplier") PartyRole supplier, @Param("cooperation") Cooperation cooperation, @Param("product") Product product);

    @Query("select e from InventoryBatch e where e.company = :company and e.supplier = :supplier and e.product = :product and e.balanceQty - e.frostQty > 0 order by e.batchNumber desc, e.batchSerial desc ")
    List<InventoryBatch> findInventoryBatchForPurchaseReturn(@Param("company") PartyRole company, @Param("supplier") PartyRole supplier, @Param("product") Product product);

    @Query("select t from InventoryBatch t where t.company.id = ?1 and t.dept.id = ?2 and t.supplier.id = ?3 and t.product.id = ?4 and t.cooperation.id = ?5 and t.balanceQty > t.frostQty")
    Page<InventoryBatch> findReturnableByCompanyIdAndDeptIdAndSupplierIdAndProductIdAndCooperationId(String companyId, String deptId, String supplierId, String productId, String cooperationId, Pageable pageable);

    @Query("select t from InventoryBatch t where t.supplier.id = ?1 and t.product.id = ?2 and t.cooperation.id = ?3")
    List<InventoryBatch> findChangePriceAbleBatch(/*String deptId,*/ String supplierId, String productId, String cooperationId);

	    @Query("select t from InventoryBatch t where t.company.id = :company_id and t.supplier.id = :supplier_id and (t.product.id = :product_id or t.product.name like %:product_name%) " +
            " and t.cooperation.id = :cooperation_id and t.balanceQty > t.frostQty")
    Page<InventoryBatch> findAvailInventoryBatch(@Param("company_id") String company_id, @Param("supplier_id") String supplier_id, @Param("product_id") String product_id,
                                                 @Param("product_name") String product_name, @Param("cooperation_id") String cooperation_id, Pageable pageable);

    @Query("select t from InventoryBatch t where t.company.id = :company_id and t.supplier.id = :supplier_id " +
            " and t.cooperation.id = :cooperation_id and t.balanceQty > t.frostQty")
    Page<InventoryBatch> findMultipleProductAvailInventoryBatch(@Param("company_id") String company_id, @Param("supplier_id") String supplier_id,
                                                 @Param("cooperation_id") String cooperation_id, Pageable pageable);


    @Query("select t from InventoryBatch t where t.supplier.id = ?1 and t.product.id = ?2 and t.cooperation.id = ?3")
    List<InventoryBatch> findChangePriceAbleBatchByCompany( String supplierId, String productId, String cooperationId);

    @Query(value = "SELECT nvl(max(BATCH_SERIAL),0)+1 AS serial FROM ACC_INVENTORY_BATCH t WHERE t.PRODUCT_ID = ?1 AND t.BATCH_NUMBER = ?2", nativeQuery = true)
    Integer findMaxBatchSerial(String productId, Long batchNumber);

    @Query("select t from InventoryBatch t where t.company.id = ?1 and t.product.id = ?2  and t.balanceQty > t.frostQty")
    Page<InventoryBatch> findReturnableByCompanyIdAndProductId(String companyId, String productId,  Pageable pageable);

    @Query(value = "SELECT * FROM V_PRODUCTLOSS_STOCK WHERE 1 = 1 AND LOCATION_ID = ?1 ", nativeQuery = true, // AND COOPERATION_ID = ?2
    countQuery = "SELECT COUNT(*) FROM V_PRODUCTLOSS_STOCK WHERE 1 = 1 AND LOCATION_ID = ?1 ") // AND COOPERATION_ID = ?2
    Page<Map> findProductLoss(String location, /*String cooperationId,*/ Pageable pageable);

    @Query(value = "SELECT * FROM V_PRODUCTLOSS_STOCK WHERE 1 = 1 AND LOCATION_ID = ?1 AND PRODUCT_ID = ?2 ", nativeQuery = true, // AND COOPERATION_ID = ?3
    countQuery = "SELECT COUNT(*) FROM V_PRODUCTLOSS_STOCK WHERE 1 = 1 AND LOCATION_ID = ?1 AND PRODUCT_ID = ?2 ") // AND COOPERATION_ID = ?3
    Page<Map> findProductLossProduct(String location, String product, /*String cooperationId,*/ Pageable pageable);

    @Query(value = "SELECT * FROM V_PRODUCTLOSS_STOCK WHERE 1 = 1 AND LOCATION_ID = ?1 AND PRODUCT_ID = ?2 ", nativeQuery = true)
    List<Map> findProductLossProduct(String location, String product);
    /**
     * fetch data by company id and product id and supplier id
     *
     * @param companyId 公司编码
     * @param productId 商品编码
     * @param supplierId 供应商编码
     * @param pageable 分页条件
     * @return  Page<InventoryBatch>
     */
    @Query("select t from InventoryBatch t where t.company.id = ?1 and t.product.id = ?2 and t.supplier.id =?3 and t.balanceQty > t.frostQty")
    Page<InventoryBatch> findReturnableByCompanyIdAndProductIdAndSupplier(String companyId, String productId,String supplierId,  Pageable pageable);

    /**
     * fetch data by company id and product id
     *
     * @param companyId 公司编码
     * @param productId 商品编码
     * @param pageable 分页条件
     * @return  Page<InventoryBatch>
     */
    @Query("select t from InventoryBatch t where t.company.id = ?1 and t.product.id = ?2  and t.balanceQty > t.frostQty and t.supplier.id <>'A00000' ")
    Page<InventoryBatch> findReturnableByCompanyIdAndProductIdNotInSupplierId(String companyId, String productId,  Pageable pageable);

    /**
     * fetch data by company  and consignment  and  supplier
     *
     * @param companyId 公司编码
     * @param supplierId 供应商编码
     * @param cooperationId 合作方式编码
     * @return  double
     */
    @Query(value = "select nvl(sum(balance_qty*tax_cost_price),0) standingInventoryAmount from acc_inventory_batch where  COMPANY_ID= ?1 and SUPPLIER_ID =?2 and COOPERATION_ID =?3",nativeQuery = true)
    double findStandingInventoryAmount(String companyId,String supplierId ,String cooperationId);

    /**
     * fetch data by consignment  and  supplier
     *
     * @param supplierId 供应商编码
     * @param cooperationId 合作方式编码
     * @return  double
     */
    @Query(value = "select nvl(sum(balance_qty*tax_cost_price),0) standingInventoryAmount from acc_inventory_batch where  SUPPLIER_ID =?1 and COOPERATION_ID =?2",nativeQuery = true)
    double findStandingInventoryAmount(String supplierId ,String cooperationId);

    /**
     * fetch data by company id and consignment type
     *
     * @param companyId 公司编码
     * @return  double
     */
    @Query(value = "select nvl(sum(balance_qty*tax_cost_price),0) standingInventoryAmount from acc_inventory_batch where  COMPANY_ID= ?1 and COOPERATION_ID ='consignment' ",nativeQuery = true)
    double findStandingInventoryAmount(String companyId);

    /**
     * fetch data by company id and supplier id and cooperation id  and  product id
     *
     * @param companyId 公司编码
     * @param supplierId 供应商编码
     * @param cooperationId 合作方式编码
     * @param productId 商品编码
     * @return  InventoryBatch
     */
    @Query("select e from InventoryBatch e where e.company.id = :companyId and e.supplier.id = :supplierId and e.cooperation.id = :cooperationId and e.product.id = :productId and e.balanceQty - e.frostQty > 0 order by e.batchNumber asc, e.batchSerial asc ")
    List<InventoryBatch> findInventoryByPurchaseReturn(@Param("companyId") String companyId,  @Param("supplierId") String supplierId, @Param("cooperationId") String cooperationId, @Param("productId") String productId);

    /**
     * fetch data by company id and product id and supplier id
     *
     * @param companyId 公司编码
     * @param productId 商品编码
     * @param supplierId 供应商编码
     * @return  List<InventoryBatch>
     */
    @Query("select t from InventoryBatch t where t.company.id = ?1 and t.product.id = ?2 and t.supplier.id =?3 and t.balanceQty > t.frostQty")
    List<InventoryBatch> findReturnableByCompanyIdAndProductIdAndSupplier(String companyId, String productId,String supplierId);

    /**
     * fetch data by id and supplier id
     *
     * @param supplierId 供应商编码
     * @param createDate 创建时间
     * @param pageable 分页条件
     * @return  Page<InventoryBatch>
     */
    @Query("select t from InventoryBatch t where t.supplier.id = ?1 and t.originalSupplier is null and t.createDate>=?3 and t.id >?2")
    Page<InventoryBatch> findAllBySupplierIdOrderById(String supplierId, long id, Date createDate, Pageable pageable);

    @Query("select t from InventoryBatch t where t.createDate=(select min (createDate) from InventoryBatch where supplier.id='A1001' and originalSupplier is null ) and t.supplier.id = 'A1001' and t.originalSupplier is null")
    List<InventoryBatch> findBySupplierAndMinCreateDate();

    @Query(value = "SELECT C.* " +
            "  FROM ACC_VOUCHER A, ACC_VOUCHER_ITEM B, ACC_INVENTORY_BATCH C " +
            " WHERE A.ID = B.VOUCHER_ID " +
            "   AND B.ID = C.BATCH_NUMBER " +
            "   AND A.SOURCE = ?1 ", nativeQuery = true)
    List<InventoryBatch> accordPickGetInventoryBatch(@Param("pickId")String pickId);

    @Query(value = "" +
            "SELECT CASE WHEN SUM(BALANCE_QTY - FROST_QTY) > MAX(B.USABLE_QTY) THEN MAX(B.USABLE_QTY) ELSE SUM(BALANCE_QTY - FROST_QTY) END USABLE_QTY " +
            "  FROM ACC_INVENTORY_BATCH A, ACC_STOCK B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID AND A.PRODUCT_ID = B.PRODUCT_ID AND B.USABLE_QTY > 0  " +
            "   AND B.COMPANY_ID = :companyId AND B.PRODUCT_ID = :productId " +
            "   AND SUPPLIER_ID = :supplierId AND B.LOCATION_ID = :locationId " +
            " HAVING SUM(BALANCE_QTY - FROST_QTY) > 0 ", nativeQuery = true)
    Map sumSupplierInventory(@Param("productId") String productId, @Param("companyId") String companyId, @Param("supplierId") String supplierId, @Param("locationId") String locationId);

    List<InventoryBatch> findAllByBatchNumberInOrderByBatchNumberAscBatchSerialAsc(List<Long> batchNumbers);

    @Query(value = "" +
            "SELECT BALANCE_QTY, FROST_QTY, BALANCE_QTY - FROST_QTY SURPLUS_BALANCE_QTY, PRODUCT_UOM_ID FROM ACC_INVENTORY_BATCH WHERE COMPANY_ID = :companyId AND PRODUCT_ID = :productId AND BALANCE_QTY - FROST_QTY > 0 ", nativeQuery = true)
    List<Map> queryInventoryBatch(@Param("companyId") String companyId, @Param("productId") String productId);

    @Query(value = "" +
            "SELECT BALANCE_QTY, FROST_QTY, BALANCE_QTY - FROST_QTY SURPLUS_BALANCE_QTY, PRODUCT_UOM_ID FROM ACC_INVENTORY_BATCH WHERE COMPANY_ID = :companyId AND PRODUCT_ID = :productId AND SUPPLIER_ID IN (:supplierIds) AND BALANCE_QTY - FROST_QTY > 0 ", nativeQuery = true)
    List<Map> queryInventoryBatch(@Param("companyId") String companyId, @Param("productId") String productId, @Param("supplierIds") List supplierIds);
}
